<?include('top.php');
	include('conn.php');

	session_start();
	?>
<?php

//include the following 2 files
require 'PHPExcel_1.7.9_doc/Classes/PHPExcel.php';
require_once 'PHPExcel_1.7.9_doc/Classes/PHPExcel/IOFactory.php';

//db connection
//$conn = mysql_connect("50.63.244.141","invoiceappdev"," Admin1@3");
//mysql_select_db("invoiceappdev",$conn);
include('conn.php');

//fetch values from HTML page
$srow=$_POST['srow'];
$dropdownval=$_POST['menu'];

$tname=$_POST['tname'];
$qrybatch = " SELECT CASE WHEN batch IS NULL
THEN 1001
ELSE max( batch ) +1
END AS batchid
FROM `invoice3`";
$result = mysql_query($qrybatch);
$value = mysql_fetch_object($result);
$batch = $value->batchid;
$ivcount=0;
$total=0;



$allowedExts = array("gif", "jpeg", "jpg", "png","xls");
$temp = explode(".", $_FILES["file"]["name"]);
$extension = end($temp);
if ((($_FILES["file"]["type"] == "image/gif")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/jpg")
|| ($_FILES["file"]["type"] == "image/pjpeg")
|| ($_FILES["file"]["type"] == "image/x-png")
|| ($_FILES["file"]["type"] == "application/vnd.ms-excel"))
&& ($_FILES["file"]["size"] < 2000000)
&& in_array($extension, $allowedExts))
  {
  if ($_FILES["file"]["error"] > 0)
    {
    echo "Return Code: " . $_FILES["file"]["error"] . "<br>";
    }
  else
    {
    echo "<b>File Uploaded: " . $_FILES["file"]["name"] . "</b><br>";
    echo "Type: " . $_FILES["file"]["type"] . "<br>";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
    //echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br>";

    if (file_exists("" . $_FILES["file"]["name"]))
      {
      echo $_FILES["file"]["name"] . " already exists. ";
      }
    else
      {
      move_uploaded_file($_FILES["file"]["tmp_name"],
      "" . $_FILES["file"]["name"]);
      echo "Stored in: " . "" . $_FILES["file"]["name"]."<br>";
      }
    }
  }
  
else
  {
  echo "Invalid file";
  }
  

//it will create table when user enter Table name

$createTable="CREATE TABLE $tname(
 `Batch` INT (10) NOT NULL,
 `Bkng Date` DATE NOT NULL ,
 `ID Number` INT NOT NULL ,
 `Pax Name` VARCHAR( 100 ) NOT NULL ,
 `Ticket Number` VARCHAR( 100 ) NOT NULL ,
 `PNR` VARCHAR( 50 ) NOT NULL ,
 `Sector` VARCHAR( 50 ) NOT NULL ,
 `Travel Date` DATE NOT NULL ,
 `Basic` DECIMAL NOT NULL ,
 `Tax` DECIMAL NOT NULL ,
 `Net Payable` DECIMAL NOT NULL)";
 
$file = ''.$_FILES["file"]["name"];

if($dropdownval!='none' &&$tname==TRUE)
{
echo "Either select from Dropdown or write table name in textfield";
}

else
{
mysql_query("BEGIN");
$objPHPExcel = PHPExcel_IOFactory::load($file);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns = ord($highestColumn) - 64;
    echo "<br>The worksheet ".$worksheetTitle." has ";
    echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
    echo ' and ' . $highestRow . ' row.<BR><BR>';
}
echo '<table><tr><td>';
echo 'Data:<BR><BR><BR><table width="100%" cellpadding="3" cellspacing="0" border=1 bordercolor="green"><tr>';
for ($row = $srow-1; $row <= $highestRow-2; ++ $row) {
   echo '<tr>';
   for ($col = 0; $col < $highestColumnIndex; ++ $col) {
       $cell = $worksheet->getCellByColumnAndRow($col, $row);
	   
       $val = $cell->getValue();
       if($row === 1)
            echo '<td style="background:#000; color:#fff;">' . $val . '</td>';
       else
           echo '<td>' . $val . '</td>';
   }
   
echo '</tr>';
}
echo '</table>';
echo '</td>';
echo '<td>';

echo '<BR><BR><table cellpadding="3" cellspacing="0" border=1 bordercolor="black"><tr>';
echo '<th>' . "Uploaded Result Status" . '</th><br>';
echo '</tr>';
for ($row = $srow; $row <=$highestRow-2; ++ $row) {
	$val=array();
echo '<tr>';
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
   $cell = $worksheet->getCellByColumnAndRow($col, $row);
   $val[] = $cell->getValue();
}
if(empty($val[0]) or empty($val[1]) or empty($val[3]) or empty($val[4]) or empty($val[5]) or empty($val[6]) or empty($val[7]) or empty($val[8]) or empty($val[9]) or ($val[10]<'0') or empty($val[18]))
{
echo '<tr>';
echo '<td bgcolor="red" ><font color="white">' . "Invalid Row" . '</font></td>';
$ivcount++;
}
else
echo '<td bgcolor="green"><font color="white">' . "Valid Row" . '</font></td>';
}
echo '</tr>';
echo '</table>';
echo '</td>';
echo '</tr>';
echo '</table>';


if($dropdownval!='none' && $tname==FALSE)
{

for ($row = $srow; $row <= $highestRow-2; ++ $row) {
	$val=array();
	 
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
   $cell = $worksheet->getCellByColumnAndRow($col, $row);
   $val[] = $cell->getValue();

}

$total++;


//comaa testing starts
$arr=array(0,1,2,3,4,5,6,7,8,9,10,18);
foreach($arr as $value)
{
if(strpos($val[$value],","))
$val[$value]=str_replace(",","",$val[$value]);
}
//comma testing ends
//Dropdown table query starts here...
	$sql="INSERT INTO $dropdownval(`batch`,`Bkng Date`,`ID Number`,`Pax Name`,`Ticket Number`,`PNR`,`Sector`,`Travel Date`,`Basic`,`Tax`,`Net Payable`)
VALUES('".$batch."','".$val[0]."','".$val[1]."','".$val[3]."','" .$val[4]."','".$val[5]."','".$val[6]."','".$val[7]."','".$val[9]."','".$val[10]."','".$val[18]."')";

    mysql_query($sql);
	
$delete="delete from $dropdownval where `PNR`='' OR `Ticket Number`='' OR `Bkng Date`='' OR `ID Number`='' OR `Pax Name`='' OR `Sector`='' OR `Travel Date`='' OR `Basic`='' OR `Net Payable`='' OR `Tax` IS NULL ";
  mysql_query($delete);
	
	}
	
		$update="update $dropdownval set batch = batch + 1";
		mysql_query($update);
	
	//Dropdown table query ends here...
		
		$vcount=$total-$ivcount;
	echo "<br>Total No. Of Rows To Be Uploaded : ".$total."<br><br>";
	echo "Valid Rows : ".$vcount."<br><br>";
	echo "Invalid Rows : ".$ivcount."<br><br>";
		
		if((@$sql)and(@$delete)and(@$update))
	{
	echo "$vcount Out Of $total Rows Uploaded<br><br>";
 mysql_query("COMMIT");
	echo "<BR><font color='green'><BIG>Successfully Uploaded ".$file." into table ".$dropdownval;
	
	echo "</BIG></font><BR>";
		
	}
	else
	{
		$flag=false;
	  mysql_query("ROLLBACK");
		echo "Error while uploading ".$file." into table ".$dropdownval;
		}
		

	
}

else
{	
	for ($row = $srow; $row <= $highestRow-2; ++ $row) {
		
	$val=array();

for ($col = 0; $col < $highestColumnIndex; ++ $col) {
   $cell = $worksheet->getCellByColumnAndRow($col, $row);
   
   
   $val[] = $cell->getValue();
}


$total++;

//comaa testing starts
$arr=array(0,1,2,3,4,5,6,7,8,9,10,18);
foreach($arr as $value)
{
if(strpos($val[$value],","))
$val[$value]=str_replace(",","",$val[$value]);
}
//comma testing ends
//textbox table query starts here...

mysql_query($createTable);

$sql1="INSERT INTO $tname(`batch`,`Bkng Date`,`ID Number`,`Pax Name`,`Ticket Number`,`PNR`,`Sector`,`Travel Date`,`Basic`,`Tax`,`Net Payable`)
VALUES('".$batch."','".$val[0]."','".$val[1]."','".$val[3]."','" .$val[4]."','".$val[5]."','".$val[6]."','".$val[7]."','".$val[9]."','".$val[10]."','".$val[18]."')";
   mysql_query($sql1);
$delete1="delete from $tname where `PNR`='' OR `Ticket Number`='' OR `Bkng Date`='' OR `ID Number`='' OR `Pax Name`='' OR `Sector`='' OR `Travel Date`='' OR `Basic`='' OR `Net Payable`='' OR `Tax` IS NULL ";
  mysql_query($delete1);
	

	}
		$update1="update $tname set batch = batch + 1";
		mysql_query($update1);
	
	$vcount=$total-$ivcount;
	echo "<br>Total No. Of Rows To Be Uploaded : ".$total."<br><br>";
	echo "Valid Rows : ".$vcount."<br><br>";
	echo "Invalid Rows : ".$ivcount."<br>";
	
	//textbox table query ends here...
		if((@$sql1)and(@$delete1)and(@$update1))
		{
		echo "$vcount Out Of $total Rows Uploaded<br><br><br>";
		  mysql_query("COMMIT");
	echo "<BR><font color='green'><BIG>Successfully Created table $tname and Uploaded ".$file." into table ".$tname;
	
	}
	else
	{
	 mysql_query("ROLLBACK");
		echo "Error while uploading ".$file." into table ".$tname;
		}
		
		
}

	}
	
?>